package com.winning.bi.jdbc.dao;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.transaction.annotation.Isolation;
import org.springframework.transaction.annotation.Transactional;

import com.alibaba.fastjson.JSON;
import com.microsoft.sqlserver.jdbc.SQLServerException;
import com.winning.bi.etl.NewTask;
import com.winning.bi.jdbc.QuartzUtils;

/**
 * 
 * @author 张大川
 *
 */
public class DaoImp implements Dao {
	private static Logger log = LoggerFactory.getLogger(DaoImp.class);
	private DataSource datasource;
	private JdbcTemplate jdbcTemplateObject;
	private NamedParameterJdbcTemplate njdbc;

	private DefaultLobHandler lob;

	public DefaultLobHandler getLob() {
		return lob;
	}

	/**
	 * 根据map 生成 update 语句
	 * 
	 * @param tableName
	 * @param keyValueMap
	 * @param map
	 * @return
	 */
	int updateSql(String tableName, Map<String, Object> keyValueMap, Map<String, Object> map) {

		List<String> columList = new ArrayList<String>();// 字段集合

		List<String> argsList = new ArrayList<String>();// ?集合

		List<Object> valueList = new ArrayList<Object>();// 值集合
		List<String> where_columList = new ArrayList<String>();// 查询条件_字段集合
		List<String> where_argsList = new ArrayList<String>();// 查询条件_?集合
		List<Object> where_valueList = new ArrayList<Object>();// 查询条件_值集合
		StringBuilder sql = new StringBuilder("update " + tableName);
		for (Map.Entry<String, Object> entry : keyValueMap.entrySet()) {

			log.debug("Key = " + entry.getKey() + ", Value = " + entry.getValue());
			if (null != entry.getValue()) {
				columList.add(entry.getKey());
				argsList.add("?");
				valueList.add(entry.getValue());
			}
		}
		for (int i = 0; i < columList.size(); i++) {
			if (i == 0) {
				sql.append(" set " + columList.get(i) + " = " + argsList.get(i));
			} else {
				sql.append(" , " + columList.get(i) + " = " + argsList.get(i));
			}
		}

		for (Map.Entry<String, Object> entry : map.entrySet()) {
			if (null != entry.getValue()) {
				where_columList.add(entry.getKey());
				where_argsList.add("?");
				where_valueList.add(entry.getValue());
			}
		}

		for (int i = 0; i < where_columList.size(); i++) {
			if (i == 0) {
				sql.append(" where ").append(where_columList.get(i) + " = " + where_argsList.get(i));
			} else {
				sql.append(" and ").append(where_columList.get(i) + " = " + where_argsList.get(i));
			}
		}
		valueList.addAll(where_valueList);
		List<Object> finalValueList = new ArrayList<Object>();// 最终值集合
		for (Object objTemp : valueList) {
			if (null != objTemp) {
				finalValueList.add(objTemp);
			}
		}
		Object[] args = finalValueList.toArray();
		int row = 0;
		log.info("[update-sql]" + sql.toString());
		row = jdbcTemplateObject.update(sql.toString(), args);
		return row;

	}

	public void setLob(DefaultLobHandler lob) {
		this.lob = lob;
	}

	public void setdatasource(DataSource ds) {
		this.datasource = ds;
		// datasource.getConnection().getMetaData().getp
		this.setJdbcTemplateObject(new JdbcTemplate(datasource));
		this.setNjdbc(new NamedParameterJdbcTemplate(datasource));
	}

	public NamedParameterJdbcTemplate getNjdbc() {
		return njdbc;
	}

	public void setNjdbc(NamedParameterJdbcTemplate njdbc) {
		this.njdbc = njdbc;
	}

	public JdbcTemplate getJdbcTemplateObject() {
		return jdbcTemplateObject;
	}

	public void setJdbcTemplateObject(JdbcTemplate jdbcTemplateObject) {
		this.jdbcTemplateObject = jdbcTemplateObject;
	}

	@Override
	public Map<String, Object> getMap(String sql) {

		return jdbcTemplateObject.queryForMap(sql);
	}

	@Override
	public List<Map<String, Object>> getListMap(String sql, Map<String, String> fiStrings, Map<String, String> fit) {
		List<Map<String, Object>> maps = new ArrayList<>();
		jdbcTemplateObject.query(sql, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Map<String, Object> map = new HashMap<String, Object>();
				if (fiStrings != null) {
					for (Map.Entry<String, String> entry : fiStrings.entrySet()) {
						if ("timestamp".equals(entry.getValue())) {
							log.debug("[timestamp]");
							map.put(entry.getKey(),
									QuartzUtils.bytesToHexString(lob.getBlobAsBytes(rs, entry.getKey())));
						} else if ("text".equals(entry.getValue())) {
							log.debug("[text]");
							map.put(entry.getKey(), lob.getClobAsString(rs, entry.getKey()));
						} else if ("ntext".equals(entry.getValue())) {
							log.debug("[ntext]");
							map.put(entry.getKey(), lob.getClobAsString(rs, entry.getKey()));
						} else if ("image".equals(entry.getValue())) {
							log.debug("[image]");
							map.put(entry.getKey(), lob.getBlobAsBytes(rs, entry.getKey()));
						} else {
							try {
								map.put(entry.getKey(), rs.getObject(entry.getKey()));
							} catch (SQLServerException e) {
								log.debug(e.getMessage());
							}
						}
					}
				}

				if (fit != null) {

					System.out.println(JSON.toJSONString(fit));
					for (Map.Entry<String, String> entry : fit.entrySet()) {
						String string = StringUtils.remove(entry.getKey(), "]");
						String string2 = StringUtils.remove(string, "[");
						System.out.println(string2);
						if (ArrayUtils.contains(NewTask.KEYS, string2.toUpperCase())) {
							map.put(entry.getValue(), map.get(string2.toLowerCase()));
							map.remove(string2.toLowerCase());
						} else if (map.get(entry.getKey()) != null && !entry.getKey().equals(entry.getValue())) {
							map.put(entry.getValue(), map.get(entry.getKey()));
							map.remove(entry.getKey());
						}

					}
				}
				System.out.println(JSON.toJSONString(map));
				maps.add(map);

			}
		});

		return maps;

	}

	@Override
	public Map<String, String> getMetaData(String tableName) {
		String sql = "select top 1 * from " + tableName;
		log.info(sql);
		SqlRowSet rowSet = jdbcTemplateObject.queryForRowSet(sql);

		SqlRowSetMetaData data = rowSet.getMetaData();
		int count = data.getColumnCount();

		Map<String, String> map = new HashMap<String, String>();

		for (int i = 0; i < count; i++) {

			String fieldName = data.getColumnName(i + 1).toLowerCase();

			String typeName = data.getColumnTypeName(i + 1).toLowerCase();

			map.put(fieldName, typeName);

		}
		return map;

	}

	public void insertMap(Map<String, Object> map, String tableName) throws SQLException {
		SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplateObject);
		jdbcInsert.withTableName(tableName);
		Map<String, Object> map2 = new HashMap<String, Object>();
		ResultSet rs = null;
		try {
			jdbcInsert.execute(map);

		} catch (DuplicateKeyException er) {
			log.info("主键冲突，选择更新");
			try {
				/**
				 * 检测主键，遇到主键冲突，进行数据更新
				 * 
				 * 
				 */
				rs = datasource.getConnection().getMetaData().getPrimaryKeys(null, null, tableName);
				try {
					while (rs.next()) {

						String columnName = rs.getString("COLUMN_NAME");// 列名
						map2.put(columnName, map.get(columnName));
						map.remove(columnName);

					}
					int i = updateSql(tableName, map, map2);
					log.debug("[更新行]" + i);
				} catch (SQLException e) {
					throw e;
				}

			} catch (SQLException e) {
				throw e;
			}
		}

	}

	public String updateReflectObjectSql(String tableName, Map<String, Object> keyValueMap) {
		return null;
	}

	@Transactional(isolation = Isolation.SERIALIZABLE)
	public void insertList(List<Map<String, Object>> list, String tableName, String... fieldName) {
		SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplateObject);
		jdbcInsert.withTableName(tableName);
		jdbcInsert.usingColumns(fieldName);
		for (Map<String, Object> attribute : list) {
			jdbcInsert.execute(attribute);
		}

	}

	@Override
	public List<Map<String, Object>> getListMap(String sql, Object... objects) {

		return jdbcTemplateObject.queryForList(sql, objects);
	}

	@Override
	public List<Map<String, Object>> getListMapByName(String name, String[] fieldName, Object... objects) {

		return null;
	}

	@Override
	public List<Map<String, Object>> getListMapByName(String tablename, String[] fitName, Object objects) {

		return null;
	}

	@Override
	public String[] getMetDataStrings(String tableName) {
		SqlRowSet rowSet = jdbcTemplateObject.queryForRowSet("select top 1 * from " + tableName);
		SqlRowSetMetaData data = rowSet.getMetaData();
		return data.getColumnNames();
	}

	@Override
	public byte[] queryForObject(String sql) {
		jdbcTemplateObject.queryForObject(sql, byte[].class);
		return null;
	}

	@Override
	public void insertMap(Map<String, Object> map, String tableName, String... objects) throws SQLException {
		SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplateObject);
		jdbcInsert.withTableName(tableName);
		jdbcInsert.usingColumns(objects);
		Map<String, Object> map2 = new HashMap<String, Object>();
		ResultSet rs = null;
		try {
			log.info("[插入数据]" + JSON.toJSONString(map) + "[插入的表]" + tableName);

			int i = jdbcInsert.execute(map);
			log.info("[影响的行]" + i);

		} catch (DuplicateKeyException er) {
			log.error(er.getMessage());
			log.info("主键冲突，选择更新");

			rs = datasource.getConnection().getMetaData().getPrimaryKeys(null, null, tableName);// 获取主键，根据主键更新
			while (rs.next()) {

				String columnName = rs.getString("COLUMN_NAME");// 列名
				map2.put(columnName, map.get(columnName));
				map.remove(columnName);

			}

			int i = updateSql(tableName, map, map2);
			log.info("[更新行]" + i);
		} catch (Exception e) {
			log.info(e.getMessage());
			throw e;
		}

	}

	@Override
	public void insetSql(String sql, Object[] objects) {

		int temp = jdbcTemplateObject.update(sql, objects);
		log.debug(temp + "");
	}

	@Override
	public byte[] getMaxVersion(String table) {
		String sql = "select max([timestamp]) xx from " + table;
		log.info(sql);
		return jdbcTemplateObject.queryForObject(sql, new RowMapper<byte[]>() {

			@Override
			public byte[] mapRow(ResultSet rs, int rowNum) throws SQLException {
				byte[] bs = lob.getBlobAsBytes(rs, "xx");
				return bs;
			}
		});
	}

	@Override
	public Page queryTaskInfo(Page page) throws Exception {
		String sqlCount = "select count(*)  from task_info  where 1=1 ";

		if (StringUtils.isNoneEmpty(page.getMap().get("taskname"))) {
			sqlCount = sqlCount + " and jobname= '" + page.getMap().get("taskname") + "'";
		}
		if (StringUtils.isNoneEmpty(page.getMap().get("source"))) {
			sqlCount = sqlCount + " and sourcetable= '" + page.getMap().get("source") + "'";
		}
		if (StringUtils.isNoneEmpty(page.getMap().get("target"))) {
			sqlCount = sqlCount + " and sourcetable= '" + page.getMap().get("target") + "'";
		}
		log.info("[总数SQL]" + sqlCount);
		Integer integer;
		try {
			integer = jdbcTemplateObject.queryForObject(sqlCount, Integer.class);
		} catch (Exception e) {
			throw e;
		}

		String sql = "SELECT jobid, jobname, markversion,  sourcetable, targettable, queuename, mapping,updatetime ,processtime from task_info  where 1=1 ";
		if (StringUtils.isNoneEmpty(page.getMap().get("taskname"))) {
			sql = sql + " and jobname= '" + page.getMap().get("taskname") + "'";
		}
		if (StringUtils.isNoneEmpty(page.getMap().get("source"))) {
			sql = sql + " and sourcetable= '" + page.getMap().get("source") + "'";
		}
		if (StringUtils.isNoneEmpty(page.getMap().get("target"))) {
			sql = sql + " and sourcetable= '" + page.getMap().get("target") + "'";
		}

		sql = sql + " ORDER BY updatetime desc OFFSET " + page.getStartIndex() + " ROW FETCH NEXT " + page.getPageSize()
				+ " ROWS ONLY";

		List<Object[]> list = new ArrayList<Object[]>();

		jdbcTemplateObject.query(sql, new RowCallbackHandler() {

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				Object[] objects = new Object[9];
				for (int i = 1; i < objects.length + 1; i++) {
					// System.out.println(rs.getObject(1));
					objects[i - 1] = rs.getObject(i);
					// System.out.println(rs.getObject(i));
				}
				list.add(objects);
			}

		});
		Object[][] rowData = new Object[list.size()][];
		for (int i = 0; i < list.size(); i++) {
			Object[] string = list.get(i);
			rowData[i] = string;

		}
		page.setStrings(rowData);
		page.setTotalRecord(integer);
		return page;
	}

	@Override
	public String[][] getMetaDataArray(String tableName) {
		SqlRowSet rowSet = jdbcTemplateObject.queryForRowSet("select top 1 * from " + tableName);
		SqlRowSetMetaData data = rowSet.getMetaData();

		int count = data.getColumnCount();

		String[][] map = new String[count][];

		for (int i = 0; i < count; i++) {
			String[] strings = new String[3];
			strings[0] = data.getColumnName(i + 1).toLowerCase();

			strings[1] = data.getColumnTypeName(i + 1);
			strings[2] = data.getColumnDisplaySize(i + 1) + "";
			// data.get
			map[i] = strings;
			// map.put(fieldName, typeName);

		}
		return map;
	}

	@Override
	public DatabaseMetaData getDataBaseMetaData(DataSource source) throws SQLException {

		return source.getConnection().getMetaData();
	}

	@Override
	public String[][] getTableNames(DataSource dataSource, String schemaName) throws SQLException {
		DatabaseMetaData data = dataSource.getConnection().getMetaData();
		List<String[]> map = new ArrayList<String[]>();
		try {

			String[] types = { "TABLE" };
			ResultSet rs = data.getTables(null, schemaName, "%", types);
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");

				String remarks = rs.getString("REMARKS");
				String[] strings = { tableName, remarks };
				// map.put("TABLE_NAME", tableName);
				// map.put("REMARKS", remarks);
				map.add(strings);
			}
		} catch (SQLException e) {

			e.printStackTrace();
		}
		String[][] tableNames = new String[map.size()][];
		for (int i = 0; i < map.size(); i++) {
			tableNames[i] = map.get(i);
		}
		return tableNames;
	}

	@Override
	public int deleteTask(String taskId) {
		String sql = "DELETE FROM task_info where jobid='" + taskId + "'";
		log.info("[delete-sql]" + sql);
		int i = jdbcTemplateObject.update(sql);
		return i;
	}

	@Override
	public List<Map<String, Object>> taskInfo() {
		String sql = "SELECT jobid, jobname, markversion,  sourcetable, targettable, queuename, mapping,updatetime,cron from task_info";
		return jdbcTemplateObject.queryForList(sql);

	}

	@Override
	public Long getCountNumber(String sql) {
		return jdbcTemplateObject.queryForObject(sql, Long.class);

	}

	@Override
	public int updatetaskInfo(String sql, Object... objects) {

		return jdbcTemplateObject.update(sql, objects);
	}

	@Override
	public Page queryTaskLog(Page objects) throws Exception {
		String countsql = " select count(*) from task_log where taskid='" + objects.getMap().get("taskid") + "'";
		log.info("[总数SQL]" + countsql);
		Integer integer;
		try {
			integer = jdbcTemplateObject.queryForObject(countsql, Integer.class);
			log.info("总数" + integer);
		} catch (Exception e) {
			throw e;
		}
		//
		String sql = "SELECT taskname, executetime, beginstamp, endstamp, amount FROM task_log  where taskid='"
				+ objects.getMap().get("taskid");
		sql = sql + "' ORDER BY executetime desc OFFSET " + objects.getStartIndex() + " ROW FETCH NEXT "
				+ objects.getPageSize() + " ROWS ONLY";
		log.info(sql);

		List<Object[]> list = new ArrayList<Object[]>();
		//
		jdbcTemplateObject.query(sql, new RowCallbackHandler() {
			//
			// @Override
			public void processRow(ResultSet rs) throws SQLException {
				Object[] objects = new Object[5];
				for (int i = 1; i < objects.length + 1; i++) {
					// // System.out.println(rs.getObject(1));
					objects[i - 1] = rs.getObject(i);
					// System.out.println( rs.getObject(i));
				}
				list.add(objects);
			}
			//
		});
		// targettable, queuename, mapping,updatetime ,processtime from
		// task_info where 1=1 ";
		// if (StringUtils.isNoneEmpty(page.getMap().get("taskname"))) {
		// sql = sql + " and jobname= '" + page.getMap().get("taskname") + "'";
		// }
		// if (StringUtils.isNoneEmpty(page.getMap().get("source"))) {
		// sql = sql + " and sourcetable= '" + page.getMap().get("source") +
		// "'";
		// }
		// if (StringUtils.isNoneEmpty(page.getMap().get("target"))) {
		// sql = sql + " and sourcetable= '" + page.getMap().get("target") +
		// "'";
		// }
		//
		// sql = sql + " ORDER BY updatetime desc OFFSET " +
		// page.getStartIndex() + " ROW FETCH NEXT " + page.getPageSize()
		// + " ROWS ONLY";
		// // List<> list = jdbcTemplateObject.queryForObject(sql,
		// requiredType)\
		// System.out.println(sql);
		// // ArrayList<String> list=new ArrayList<String>();
		// // SqlRowSet rowSet= jdbcTemplateObject.queryForRowSet(sql);
		// List<Object[]> list = new ArrayList<Object[]>();
		//
		// jdbcTemplateObject.query(sql, new RowCallbackHandler() {
		//
		// @Override
		// public void processRow(ResultSet rs) throws SQLException {
		// Object[] objects = new Object[9];
		// for (int i = 1; i < objects.length + 1; i++) {
		// // System.out.println(rs.getObject(1));
		// objects[i - 1] = rs.getObject(i);
		// System.out.println( rs.getObject(i));
		// }
		// list.add(objects);
		// }
		//
		// });
		Object[][] rowData = new Object[list.size()][];
		for (int i = 0; i < list.size(); i++) {
			Object[] string = list.get(i);
			rowData[i] = string;
			//
		}
		objects.setStrings(rowData);
		objects.setTotalRecord(integer);
		return objects;
		// return null;
	}

	@Override
	public byte[] getMinVersion(String table) {
		String sql = "select min([timestamp]) xx from " + table;
		log.info(sql);
		return jdbcTemplateObject.queryForObject(sql, new RowMapper<byte[]>() {

			@Override
			public byte[] mapRow(ResultSet rs, int rowNum) throws SQLException {
				byte[] bs = lob.getBlobAsBytes(rs, "xx");
				return bs;
			}
		});
	}

}
